<?php

class vo_network extends CActiveRecord {

    public static function pin_validation($network_id, $pin) {
        $member = sys_member::model()->with('rel_member_to_serial')->find('member_network_id=:id', array(':id' => $network_id));

        if ($member == null) {
            return false;
        } else {
            if ($member->rel_member_to_serial->serial_pin === $pin) {
                return true;
            } else {
                return false;
            }
        }
    }

    public static function password_validation($network_id, $password) {
        $member = sys_password::model()->find('password_network_id=:id', array(':id' => $network_id));

        if ($member == null) {
            return false;
        } else {
            if ($member->password_value === md5($password)) {
                return true;
            } else {
                return false;
            }
        }
    }

    //data network group
    public static function get_network_group($group_network_id) {
        $sql = "SELECT network_id, network_mid FROM sys_network WHERE network_group_network_id = '" . $group_network_id . "'";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    //data group id
    public static function get_network_group_id($network_id) {
        $sql = "SELECT network_group_network_id FROM sys_network WHERE network_id = '" . $network_id . "' LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

    //update member group
    public static function update_group_profile($group_id, $obj_data) {
        $sql = "UPDATE sys_member, sys_network
            SET member_name = '" . $obj_data->member_name . "', 
                member_nickname = '" . $obj_data->member_nickname . "', 
                member_email = '" . $obj_data->member_email . "', 
                member_sex = '" . $obj_data->member_sex . "', 
                member_address = '" . $obj_data->member_address . "', 
                member_city_id = '" . $obj_data->member_city_id . "', 
                member_state_id = '" . $obj_data->member_state_id . "', 
                member_country_id = '" . $obj_data->member_country_id . "', 
                member_zipcode = '" . $obj_data->member_zipcode . "', 
                member_birth_place = '" . $obj_data->member_birth_place . "', 
                member_birth_date = '" . $obj_data->member_birth_date . "', 
                member_identity_type = '" . $obj_data->member_identity_type . "', 
                member_identity_no = '" . $obj_data->member_identity_no . "', 
                member_phone = '" . $obj_data->member_phone . "', 
                member_mobilephone = '" . $obj_data->member_mobilephone . "', 
                member_image = '" . $obj_data->member_image . "', 
                member_devisor_name = '" . $obj_data->member_devisor_name . "', 
                member_devisor_relationship = '" . $obj_data->member_devisor_relationship . "', 
                member_devisor_phone = '" . $obj_data->member_devisor_phone . "', 
                member_bank_id = '" . $obj_data->member_bank_id . "', 
                member_bank_city = '" . $obj_data->member_bank_city . "', 
                member_bank_branch = '" . $obj_data->member_bank_branch . "', 
                member_bank_account_name = '" . $obj_data->member_bank_account_name . "', 
                member_bank_account_no = '" . $obj_data->member_bank_account_no . "', 
                member_tax_account_no = '" . $obj_data->member_tax_account_no . "' 
            WHERE network_id = member_network_id 
            AND network_group_network_id = '" . $group_id . "'";
        $result = Yii::app()->db->createCommand($sql)->execute();
        if ($result) {
            return true;
        } else {
            return false;
        }
    }

    //sponsoring
    public static function get_member_data($network_id) {
        $sql = "SELECT network_id, network_mid, network_upline_network_id, network_sponsor_network_id,
            member_name, member_city_id, member_state_id, member_image,member_join_datetime FROM sys_network, sys_member
            WHERE network_id = member_network_id AND network_sponsor_network_id = '" . $network_id . "'";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }
   
    //member_name
    public static function get_member_name($network_id) {
        $sql = "SELECT member_name FROM sys_network, sys_member
            WHERE network_id = member_network_id AND network_sponsor_network_id = '" . $network_id . "'";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

    //report jaringan
    public static function get_last_wait_node($network_id, $position) {
        $sql = "SELECT netgrow_master_wait_" . $position . " FROM sys_netgrow_master WHERE netgrow_master_network_id = '" . $network_id . "' ORDER BY netgrow_master_date DESC LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

    //report jaringan
    public static function get_last_wait_node_bonus($network_id, $position) {
        $sql = "SELECT netgrow_bonus_wait_" . $position . " FROM sys_netgrow_bonus WHERE netgrow_bonus_network_id = '" . $network_id . "' ORDER BY netgrow_bonus_date DESC LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

    //report jaringan
    public static function get_wait_node($network_id, $position, $today_date) {
        $sql = "SELECT netgrow_master_wait_" . $position . " FROM sys_netgrow_master WHERE netgrow_master_network_id = '" . $network_id . "' AND netgrow_master_date < '" . $today_date . "' ORDER BY netgrow_master_date DESC LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

    //report jaringan
    public static function get_wait_node_bonus($network_id, $position, $today_date) {
        $sql = "SELECT netgrow_bonus_wait_" . $position . " FROM sys_netgrow_bonus WHERE netgrow_bonus_network_id = '" . $network_id . "' AND netgrow_bonus_date < '" . $today_date . "' ORDER BY netgrow_bonus_date DESC LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

    //report jaringan
    public static function get_netgrow_master($network_id, $date_start, $date_end) {
        $sql = "SELECT * FROM sys_netgrow_master WHERE netgrow_master_network_id = '" . $network_id . "' AND netgrow_master_date BETWEEN '" . $date_start . "' AND '" . $date_end . "' ORDER BY netgrow_master_date ASC";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    //report jaringan yang menghasilkan bonus
    public static function get_netgrow_bonus($network_id, $date_start, $date_end) {
//        $sql = "SELECT
//                sys_netgrow_bonus.*, 
//                IFNULL(netgrow_master_sponsor, 0) AS netgrow_bonus_sponsor
//            FROM sys_netgrow_bonus 
//            LEFT JOIN sys_netgrow_master 
//                ON netgrow_master_network_id = netgrow_bonus_network_id
//                AND netgrow_master_date = netgrow_bonus_date
//            WHERE netgrow_bonus_network_id = '" . $network_id . "' 
//                AND netgrow_bonus_date BETWEEN '" . $date_start . "' 
//                AND '" . $date_end . "' 
//            ORDER BY netgrow_bonus_date ASC";
        $sql = "SELECT
                    sys_netgrow_bonus.*, 
                    IFNULL(sponsor_netgrow_value, 0) AS netgrow_bonus_sponsor
                FROM sys_netgrow_bonus 
                LEFT JOIN sp_sponsor_netgrow 
                    ON sponsor_netgrow_network_id = netgrow_bonus_network_id
                    AND sponsor_netgrow_date = netgrow_bonus_date
                WHERE netgrow_bonus_network_id = '" . $network_id . "' 
                    AND netgrow_bonus_date BETWEEN '" . $date_start . "' 
                    AND '" . $date_end . "' 
                ORDER BY netgrow_bonus_date ASC";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    //report matrix
    public static function get_matrix_level($network_id, $level) {
        $matrix_network_id = function_lib::get_one("matrix_network_id", "sys_matrix_network", "matrix_network_network_id = '" . $network_id . "'");
        $sql = "SELECT matrix_network_level_node FROM sys_matrix_network_level WHERE matrix_network_level_matrix_network_id = '" . $matrix_network_id . "' AND matrix_network_level_value = '" . $level . "' LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

    //summary bonus
    public static function get_bonus_acc($network_id) {
        $sql = "SELECT
            IFNULL(sponsor_bonus_acc, 0) AS sponsor_bonus, 
            IFNULL(extra_sponsor_bonus_acc, 0) AS extra_sponsor_bonus, 
            IFNULL(gen_node_bonus_acc, 0) AS gen_node_bonus, 
            IFNULL(gen_node_products_bonus_acc, 0) AS gen_node_products_bonus, 
            IFNULL(royalti_angsuran_bonus_acc, 0) AS royalti_angsuran_bonus, 
            IFNULL(gen_node_extra_bonus_acc, 0) AS gen_node_extra_bonus, 
            IFNULL(gen_node_extra_warehouse_bonus_acc, 0) AS gen_node_extra_warehouse_bonus, 
            IFNULL(match_bonus_acc, 0) AS match_bonus, 
            IFNULL(gen_match_bonus_acc, 0) AS gen_match_bonus, 
            IFNULL(sponsor_upgrade_bonus_acc, 0) AS sponsor_upgrade_bonus, 
            (SELECT IFNULL(SUM(matrix_node_bonus_acc), 0) FROM sp_matrix_node_bonus WHERE matrix_node_bonus_network_id = network_id) AS matrix_node_bonus, 
            (SELECT IFNULL(SUM(board_level_bonus_acc), 0) FROM sp_board_level_bonus WHERE board_level_bonus_network_id = network_id) AS board_level_bonus, 
            (SELECT IFNULL(SUM(board_fly_bonus_acc), 0) FROM sp_board_fly_bonus WHERE board_fly_bonus_network_id = network_id) AS board_fly_bonus, 
            (
            IFNULL(sponsor_bonus_acc, 0) + 
            IFNULL(extra_sponsor_bonus_acc, 0) + 
            IFNULL(gen_node_bonus_acc, 0) + 
            IFNULL(gen_node_products_bonus_acc, 0) + 
            IFNULL(royalti_angsuran_bonus_acc, 0) + 
            IFNULL(gen_node_extra_bonus_acc, 0) +
            IFNULL(gen_node_extra_warehouse_bonus_acc, 0) +
            IFNULL(match_bonus_acc, 0) + 
            IFNULL(gen_match_bonus_acc, 0) + 
            IFNULL(sponsor_upgrade_bonus_acc, 0) + 
            (SELECT IFNULL(SUM(matrix_node_bonus_acc), 0) FROM sp_matrix_node_bonus WHERE matrix_node_bonus_network_id = network_id) + 
            (SELECT IFNULL(SUM(board_level_bonus_acc), 0) FROM sp_board_level_bonus WHERE board_level_bonus_network_id = network_id) + 
            (SELECT IFNULL(SUM(board_fly_bonus_acc), 0) FROM sp_board_fly_bonus WHERE board_fly_bonus_network_id = network_id)
            ) AS total_bonus 
            FROM sys_network
            LEFT JOIN sp_sponsor_bonus ON sponsor_bonus_network_id = network_id 
            LEFT JOIN sp_extra_sponsor_bonus ON extra_sponsor_bonus_network_id = network_id 
            LEFT JOIN sp_gen_node_bonus ON gen_node_bonus_network_id = network_id 
            LEFT JOIN sp_gen_node_products_bonus ON gen_node_products_bonus_network_id = network_id 
            LEFT JOIN sp_royalti_angsuran_bonus ON royalti_angsuran_bonus_network_id = network_id 
            LEFT JOIN sp_gen_node_extra_bonus ON gen_node_extra_bonus_network_id = network_id 
            LEFT JOIN sp_gen_node_extra_warehouse_bonus ON gen_node_extra_warehouse_bonus_network_id = network_id 
            LEFT JOIN sp_match_bonus ON match_bonus_network_id = network_id 
            LEFT JOIN sp_gen_match_bonus ON gen_match_bonus_network_id = network_id 
            LEFT JOIN sp_sponsor_upgrade_bonus ON sponsor_upgrade_bonus_network_id = network_id
            WHERE network_id = '" . $network_id . "'";
        $result = Yii::app()->db->createCommand($sql)->queryRow();
        return $result;
    }

    //netgrow
    public static function get_netgrow_by_date($network_id, $date) {
        $sql = "SELECT
            IFNULL(sponsor_netgrow_value, 0) AS sponsor, 
            IFNULL(extra_sponsor_netgrow_value, 0) AS extra_sponsor, 
            IFNULL(SUM(gen_node_netgrow_value), 0) AS gen_node, 
            IFNULL(SUM(gen_node_products_netgrow_value), 0) AS gen_node_products, 
            IFNULL(SUM(gen_node_extra_netgrow_value), 0) AS gen_node_extra, 
            IFNULL(SUM(gen_node_extra_warehouse_netgrow_value), 0) AS gen_node_extra_warehouse, 
            IFNULL(match_netgrow_value, 0) AS match, 
            IFNULL(SUM(gen_match_netgrow_value), 0) AS gen_match, 
            IFNULL(SUM(matrix_node_netgrow_value), 0) AS matrix_node 
            FROM sys_netgrow_master
            LEFT JOIN sp_sponsor_netgrow ON (sponsor_netgrow_network_id = netgrow_master_network_id AND sponsor_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_extra_sponsor_netgrow ON (extra_sponsor_netgrow_network_id = netgrow_master_network_id AND extra_sponsor_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_gen_node_netgrow ON (gen_node_netgrow_network_id = netgrow_master_network_id AND gen_node_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_gen_node_products_netgrow ON (gen_node_products_netgrow_network_id = netgrow_master_network_id AND gen_node_products_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_gen_node_extra_netgrow ON (gen_node_extra_netgrow_network_id = netgrow_master_network_id AND gen_node_extra_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_gen_node_extra_warehouse_netgrow ON (gen_node_extra_warehouse_netgrow_network_id = netgrow_master_network_id AND gen_node_extra_warehouse_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_match_netgrow ON (match_netgrow_network_id = netgrow_master_network_id AND match_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_gen_match_netgrow ON (gen_match_netgrow_network_id = netgrow_master_network_id AND gen_match_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_matrix_node_netgrow ON (matrix_node_netgrow_network_id = netgrow_master_network_id AND matrix_node_netgrow_date = netgrow_master_date)
            WHERE netgrow_master_network_id = '" . $network_id . "'
            AND netgrow_master_date = '" . $date . "'";
        $result = Yii::app()->db->createCommand($sql)->queryRow();
        return $result;
    }

    //bonus today
    public static function get_bonus_by_netgrow($network_id, $date) {
        /*
        $sql = "SELECT
            (IFNULL(sponsor_netgrow_value, 0) * " . Yii::app()->params['bonus']['sponsor'] . ") AS sponsor_bonus, 
            (IFNULL(extra_sponsor_netgrow_value, 0) * " . Yii::app()->params['bonus']['extra_sponsor'] . ") AS extra_sponsor_bonus, 
            (IFNULL(SUM(gen_node_netgrow_value), 0) * " . Yii::app()->params['bonus']['gen_node'] . ") AS gen_node_bonus, 
            IFNULL(SUM(gen_node_products_bonus_log_value), 0) AS gen_node_products_bonus, 
            (IFNULL(SUM(gen_node_extra_netgrow_value), 0) * " . Yii::app()->params['bonus']['gen_node_extra'] . ") AS gen_node_extra_bonus, 
            (IFNULL(match_netgrow_value, 0) * " . Yii::app()->params['bonus']['match'] . ") AS match_bonus, 
            (IFNULL(SUM(gen_match_netgrow_value), 0) * " . Yii::app()->params['bonus']['gen_match'] . ") AS gen_match_bonus, 
            (IFNULL(SUM(matrix_node_netgrow_value), 0) * " . Yii::app()->params['bonus']['matrix_node'] . ") AS matrix_node_bonus, 
            IFNULL(SUM(board_level_bonus_log_value), 0) AS board_level_bonus, 
            IFNULL(SUM(board_fly_bonus_log_value), 0) AS board_fly_bonus, 
            (
            (IFNULL(sponsor_netgrow_value, 0) * " . Yii::app()->params['bonus']['sponsor'] . ") + 
            (IFNULL(extra_sponsor_netgrow_value, 0) * " . Yii::app()->params['bonus']['extra_sponsor'] . ") + 
            (IFNULL(SUM(gen_node_netgrow_value), 0) * " . Yii::app()->params['bonus']['gen_node'] . ") + 
            IFNULL(SUM(gen_node_products_bonus_log_value), 0) + 
            (IFNULL(SUM(gen_node_extra_netgrow_value), 0) * " . Yii::app()->params['bonus']['gen_node_extra'] . ") + 
            (IFNULL(match_netgrow_value, 0) * " . Yii::app()->params['bonus']['match'] . ") + 
            (IFNULL(SUM(gen_match_netgrow_value), 0) * " . Yii::app()->params['bonus']['gen_match'] . ") + 
            (IFNULL(SUM(matrix_node_netgrow_value), 0) * " . Yii::app()->params['bonus']['matrix_node'] . ") + 
            IFNULL(SUM(board_level_bonus_log_value), 0) + 
            IFNULL(SUM(board_fly_bonus_log_value), 0)
            ) AS total_bonus 
            FROM sys_netgrow_master
            LEFT JOIN sp_sponsor_netgrow ON (sponsor_netgrow_network_id = netgrow_master_network_id AND sponsor_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_extra_sponsor_netgrow ON (extra_sponsor_netgrow_network_id = netgrow_master_network_id AND extra_sponsor_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_gen_node_netgrow ON (gen_node_netgrow_network_id = netgrow_master_network_id AND gen_node_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_gen_node_products_bonus_log ON (gen_node_products_bonus_log_network_id = netgrow_master_network_id AND gen_node_products_bonus_log_date = netgrow_master_date)
            LEFT JOIN sp_gen_node_extra_netgrow ON (gen_node_extra_netgrow_network_id = netgrow_master_network_id AND gen_node_extra_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_match_netgrow ON (match_netgrow_network_id = netgrow_master_network_id AND match_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_gen_match_netgrow ON (gen_match_netgrow_network_id = netgrow_master_network_id AND gen_match_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_matrix_node_netgrow ON (matrix_node_netgrow_network_id = netgrow_master_network_id AND matrix_node_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_board_level_bonus_log ON (board_level_bonus_log_network_id = netgrow_master_network_id AND board_level_bonus_log_date = netgrow_master_date)
            LEFT JOIN sp_board_fly_bonus_log ON (board_fly_bonus_log_network_id = netgrow_master_network_id AND board_fly_bonus_log_date = netgrow_master_date)
            WHERE netgrow_master_network_id = '" . $network_id . "'
            AND netgrow_master_date = '" . $date . "'";
        */
        $sql = "SELECT
            (IFNULL(sponsor_netgrow_value, 0) * " . Yii::app()->params['bonus']['sponsor'] . ") AS sponsor_bonus, 
            (IFNULL(extra_sponsor_netgrow_value, 0) * " . Yii::app()->params['bonus']['extra_sponsor'] . ") AS extra_sponsor_bonus, 
            IFNULL(SUM(gen_node_products_bonus_log_value), 0) AS gen_node_products_bonus, 
            (IFNULL(SUM(gen_node_extra_netgrow_value), 0) * " . Yii::app()->params['bonus']['gen_node_extra'] . ") AS gen_node_extra_bonus, 
            (IFNULL(SUM(gen_node_extra_warehouse_netgrow_value), 0) * " . Yii::app()->params['bonus']['gen_node_extra_warehouse'] . ") AS gen_node_extra_warehouse_bonus, 
            (IFNULL(SUM(gen_match_netgrow_value), 0) * " . Yii::app()->params['bonus']['gen_match'] . ") AS gen_match_bonus, 
            (IFNULL(SUM(matrix_node_netgrow_value), 0) * " . Yii::app()->params['bonus']['matrix_node'] . ") AS matrix_node_bonus, 
            IFNULL(SUM(board_level_bonus_log_value), 0) AS board_level_bonus, 
            IFNULL(SUM(board_fly_bonus_log_value), 0) AS board_fly_bonus, 
            (
            (IFNULL(sponsor_netgrow_value, 0) * " . Yii::app()->params['bonus']['sponsor'] . ") + 
            (IFNULL(extra_sponsor_netgrow_value, 0) * " . Yii::app()->params['bonus']['extra_sponsor'] . ") + 
            IFNULL(SUM(gen_node_products_bonus_log_value), 0) + 
            (IFNULL(SUM(gen_node_extra_netgrow_value), 0) * " . Yii::app()->params['bonus']['gen_node_extra'] . ") +
            (IFNULL(SUM(gen_node_extra_warehouse_netgrow_value), 0) * " . Yii::app()->params['bonus']['gen_node_extra_warehouse'] . ") +
            (IFNULL(SUM(gen_match_netgrow_value), 0) * " . Yii::app()->params['bonus']['gen_match'] . ") + 
            (IFNULL(SUM(matrix_node_netgrow_value), 0) * " . Yii::app()->params['bonus']['matrix_node'] . ") + 
            IFNULL(SUM(board_level_bonus_log_value), 0) + 
            IFNULL(SUM(board_fly_bonus_log_value), 0)
            ) AS total_bonus 
            FROM sys_netgrow_master
            LEFT JOIN sp_sponsor_netgrow ON (sponsor_netgrow_network_id = netgrow_master_network_id AND sponsor_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_extra_sponsor_netgrow ON (extra_sponsor_netgrow_network_id = netgrow_master_network_id AND extra_sponsor_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_gen_node_products_bonus_log ON (gen_node_products_bonus_log_network_id = netgrow_master_network_id AND gen_node_products_bonus_log_date = netgrow_master_date)
            LEFT JOIN sp_gen_node_extra_netgrow ON (gen_node_extra_netgrow_network_id = netgrow_master_network_id AND gen_node_extra_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_gen_node_extra_warehouse_netgrow ON (gen_node_extra_warehouse_netgrow_network_id = netgrow_master_network_id AND gen_node_extra_warehouse_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_gen_match_netgrow ON (gen_match_netgrow_network_id = netgrow_master_network_id AND gen_match_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_matrix_node_netgrow ON (matrix_node_netgrow_network_id = netgrow_master_network_id AND matrix_node_netgrow_date = netgrow_master_date)
            LEFT JOIN sp_board_level_bonus_log ON (board_level_bonus_log_network_id = netgrow_master_network_id AND board_level_bonus_log_date = netgrow_master_date)
            LEFT JOIN sp_board_fly_bonus_log ON (board_fly_bonus_log_network_id = netgrow_master_network_id AND board_fly_bonus_log_date = netgrow_master_date)
            WHERE netgrow_master_network_id = '" . $network_id . "'
            AND netgrow_master_date = '" . $date . "'";
        $result = Yii::app()->db->createCommand($sql)->queryRow();
        return $result;
    }

    //e-wallet income
    public static function get_ewallet_income_by_date($network_id, $date) {
        $sql = "SELECT *
            FROM sys_ewallet_income
            WHERE ewallet_income_network_id = '" . $network_id . "'
            AND ewallet_income_date = '" . $date . "'";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    //e-wallet income
    public static function get_ewallet_income($network_id) {
        $sql = "SELECT ewallet_income_bonus_name, SUM(ewallet_income_cash) AS ewallet_income_cash, 
                SUM(ewallet_income_noncash) AS ewallet_income_noncash,
                SUM(ewallet_income_products) AS ewallet_income_products,
                SUM(ewallet_income_voucher) AS ewallet_income_voucher
            FROM sys_ewallet_income
            WHERE ewallet_income_network_id = '" . $network_id . "'
            GROUP BY ewallet_income_bonus_name";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    //bonus log
    public static function get_bonus_log($network_id, $date_start, $date_end) {
        $sql = "SELECT
            IFNULL(sponsor_bonus_log_value, 0) AS sponsor_bonus, 
            IFNULL(extra_sponsor_bonus_log_value, 0) AS extra_sponsor_bonus, 
            IFNULL(SUM(gen_node_bonus_log_value), 0) AS gen_node_bonus, 
            IFNULL(SUM(gen_node_products_bonus_log_value), 0) AS gen_node_products_bonus, 
            IFNULL(SUM(gen_node_extra_bonus_log_value), 0) AS gen_node_extra_bonus,
            IFNULL(SUM(gen_node_extra_warehouse_bonus_log_value), 0) AS gen_node_extra_warehouse_bonus,
            IFNULL(match_bonus_log_value, 0) AS match_bonus, 
            IFNULL(SUM(gen_match_bonus_log_value), 0) AS gen_match_bonus, 
            IFNULL(SUM(matrix_node_bonus_log_value), 0) AS matrix_node_bonus, 
            IFNULL(SUM(board_level_bonus_log_value), 0) AS board_level_bonus, 
            IFNULL(SUM(board_fly_bonus_log_value), 0) AS board_fly_bonus, 
            (
            IFNULL(sponsor_bonus_log_value, 0) + 
            IFNULL(extra_sponsor_bonus_log_value, 0) + 
            IFNULL(SUM(gen_node_bonus_log_value), 0) + 
            IFNULL(SUM(gen_node_products_bonus_log_value), 0) + 
            IFNULL(SUM(gen_node_extra_bonus_log_value), 0) +
            IFNULL(SUM(gen_node_extra_warehouse_bonus_log_value), 0) +
            IFNULL(match_bonus_log_value, 0) + 
            IFNULL(SUM(gen_match_bonus_log_value), 0) + 
            IFNULL(SUM(matrix_node_bonus_log_value), 0) + 
            IFNULL(SUM(board_level_bonus_log_value), 0) + 
            IFNULL(SUM(board_fly_bonus_log_value), 0)
            ) AS total_bonus, 
            netgrow_master_date 
            FROM sys_netgrow_master
            LEFT JOIN sp_sponsor_bonus_log ON sponsor_bonus_log_network_id = netgrow_master_network_id AND sponsor_bonus_log_date = netgrow_master_date 
            LEFT JOIN sp_extra_sponsor_bonus_log ON extra_sponsor_bonus_log_network_id = netgrow_master_network_id AND extra_sponsor_bonus_log_date = netgrow_master_date 
            LEFT JOIN sp_gen_node_bonus_log ON gen_node_bonus_log_network_id = netgrow_master_network_id AND gen_node_bonus_log_date = netgrow_master_date 
            LEFT JOIN sp_gen_node_products_bonus_log ON gen_node_products_bonus_log_network_id = netgrow_master_network_id AND gen_node_products_bonus_log_date = netgrow_master_date 
            LEFT JOIN sp_gen_node_extra_bonus_log ON gen_node_extra_bonus_log_network_id = netgrow_master_network_id AND gen_node_extra_bonus_log_date = netgrow_master_date 
            LEFT JOIN sp_gen_node_extra_warehouse_bonus_log ON gen_node_extra_warehouse_bonus_log_network_id = netgrow_master_network_id AND gen_node_extra_warehouse_bonus_log_date = netgrow_master_date 
            LEFT JOIN sp_match_bonus_log ON match_bonus_log_network_id = netgrow_master_network_id AND match_bonus_log_date = netgrow_master_date 
            LEFT JOIN sp_gen_match_bonus_log ON gen_match_bonus_log_network_id = netgrow_master_network_id AND gen_match_bonus_log_date = netgrow_master_date 
            LEFT JOIN sp_matrix_node_bonus_log ON matrix_node_bonus_log_network_id = netgrow_master_network_id AND matrix_node_bonus_log_date = netgrow_master_date 
            LEFT JOIN sp_board_level_bonus_log ON board_level_bonus_log_network_id = netgrow_master_network_id AND board_level_bonus_log_date = netgrow_master_date 
            LEFT JOIN sp_board_fly_bonus_log ON board_fly_bonus_log_network_id = netgrow_master_network_id AND board_fly_bonus_log_date = netgrow_master_date 
            WHERE netgrow_master_network_id = '" . $network_id . "'
            AND netgrow_master_date BETWEEN '" . $date_start . "' AND '" . $date_end . "'";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    public static function get_board_network_id($board_network_network_mid) {
        $sql = "SELECT board_network_id FROM sys_board_network WHERE board_network_network_mid = '" . $board_network_network_mid . "' LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

    public static function get_board_network_mid($board_network_id) {
        $sql = "SELECT board_network_network_mid FROM sys_board_network WHERE board_network_id = '" . $board_network_id . "' LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

    public static function get_board_network_network_id($board_network_id) {
        $sql = "SELECT board_network_member_id FROM sys_board_network WHERE board_network_id = '" . $board_network_id . "' LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

    public static function check_network_group($board_leader_board_network_id, $board_network_id, $is_cycle) {
//        $sql = "SELECT board_id FROM sys_board
//            WHERE board_leader_board_network_id = '" . $board_leader_board_network_id . "' 
//            AND board_network_group LIKE '% " . $board_network_id . " %' 
//            LIMIT 1";
        $sql = "SELECT board_id FROM sys_board
            WHERE board_network_group LIKE '% " . $board_network_id . " %' AND board_is_fly = '$is_cycle'
            LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

    public static function get_board_network($network_id, $is_fly = '', $board_class='') {
        $sql = "SELECT * FROM sys_board_network
            WHERE board_network_member_id = '" . $network_id . "'";

        if ($is_fly != '') {
            $sql .= " AND board_network_is_fly = '" . $is_fly . "'";
        }

        if ($board_class != '') {
            $sql .= " AND board_network_board_class = '$board_class'";
        }


        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    public static function get_data_board_network_id($board_network_id, $is_fly = '0', $board_class='') {
        $sql = "SELECT * FROM sys_board_network
            WHERE board_network_id = '" . $board_network_id . "'";

        if ($is_fly != '') {
            $sql .= " AND board_network_is_fly = '" . $is_fly . "'";
        }

        if ($board_class != '') {
            $sql .= " AND board_network_board_class = '$board_class'";
        }


        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    public static function get_board_active($board_class, $network_id) {
        $sql = "SELECT * FROM sys_board
            WHERE board_class = '" . $board_class . "' 
            AND board_network_group LIKE '% " . $network_id . " %'
            AND board_is_fly = '0'";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    public static function get_board_roll($board_class, $network_id) {
        $sql = "SELECT * FROM sys_board
            WHERE board_class = '" . $board_class . "' 
            AND board_leader_board_network_id = '" . $network_id . "' 
            AND board_is_fly = '1'";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    public static function get_board_sponsor_count($board_network_id) {
        $sql = "SELECT COUNT(*) AS sponsor_count FROM sys_board_network_log WHERE board_network_log_sponsor_board_network_id = '" . $board_network_id . "' LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }
    public static function get_accumulative_sponsor_board($network_id,$board='1'){
        $accumulative_sponsor = function_lib::get_one('count(*)', 'sys_board_network_log','board_network_log_sponsor_board_network_id IN (select board_network_id from sys_board_network where board_network_member_id = \''.$network_id.'\' and board_network_board_class = \'' . $board . '\')');
        return $accumulative_sponsor;
    }
    

    public static function get_board_roll_count($board_network_id) {
        $sql = "SELECT COUNT(*) AS roll_count FROM sys_board_roll WHERE board_roll_board_network_id = '" . $board_network_id . "' LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

    public static function get_bonus_gen_node($network_id) {
        $sql = "SELECT gen_node_bonus_log_level_depth, SUM(gen_node_bonus_log_value) AS gen_node_bonus_value
                FROM sp_gen_node_bonus_log
                WHERE gen_node_bonus_log_network_id = '" . $network_id . "'
                    AND gen_node_bonus_log_level_depth > 0
                GROUP BY gen_node_bonus_log_level_depth 
                ORDER BY gen_node_bonus_log_level_depth";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    public static function get_bonus_gen_node_extra($network_id) {
        $sql = "SELECT gen_node_extra_bonus_log_level_depth, SUM(gen_node_extra_bonus_log_value) AS gen_node_extra_bonus_value
                FROM sp_gen_node_extra_bonus_log
                WHERE gen_node_extra_bonus_log_network_id = '" . $network_id . "'
                GROUP BY gen_node_extra_bonus_log_level_depth 
                ORDER BY gen_node_extra_bonus_log_level_depth";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }
    
    public static function get_bonus_gen_node_extra_warehouse($network_id) {
        $sql = "SELECT gen_node_extra_warehouse_bonus_log_level_depth, SUM(gen_node_extra_warehouse_bonus_log_value) AS gen_node_extra_warehouse_bonus_value
                FROM sp_gen_node_extra_warehouse_bonus_log
                WHERE gen_node_extra_warehouse_bonus_log_network_id = '" . $network_id . "'
                GROUP BY gen_node_extra_warehouse_bonus_log_level_depth 
                ORDER BY gen_node_extra_warehouse_bonus_log_level_depth";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    public static function get_bonus_gen_node_products($network_id) {
        $sql = "SELECT gen_node_products_bonus_log_level_depth, SUM(gen_node_products_bonus_log_value) AS gen_node_products_bonus_value
                FROM sp_gen_node_products_bonus_log
                WHERE gen_node_products_bonus_log_network_id = '" . $network_id . "'
                GROUP BY gen_node_products_bonus_log_level_depth 
                ORDER BY gen_node_products_bonus_log_level_depth";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }
    
    public static function get_bonus_royalti_angsuran($network_id,$periode){
        $sql = "SELECT royalti_angsuran_bonus_log_level_depth, royalti_angsuran_bonus_log_value,royalti_angsuran_bonus_log_date
                FROM sp_royalti_angsuran_bonus_log
                WHERE royalti_angsuran_bonus_log_network_id = '$network_id'
                AND MONTH(royalti_angsuran_bonus_log_date) = '".$periode['month']."'
                AND YEAR(royalti_angsuran_bonus_log_date) = '".$periode['year']."'
                ORDER BY royalti_angsuran_bonus_log_level_depth    
                ";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    public static function get_bonus_board_level($network_id, $level) {
        $sql = "SELECT IFNULL(SUM(board_level_bonus_log_value), 0) AS board_level_bonus_value
                FROM sp_board_level_bonus_log
                LEFT JOIN sys_board ON board_level_bonus_log_board_id = board_id
                WHERE board_level_bonus_log_network_id = '" . $network_id . "'
                AND board_class = '" . $level . "'";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

    public static function get_bonus_board_fly($network_id, $level) {
        $sql = "SELECT IFNULL(SUM(board_fly_bonus_log_value), 0) AS board_fly_bonus_value
                FROM sp_board_fly_bonus_log
                LEFT JOIN sys_board ON board_fly_bonus_log_board_id = board_id
                WHERE board_fly_bonus_log_network_id = '" . $network_id . "'
                AND board_class = '" . $level . "'";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }
    /*
     * peringkat
     */
    public static function get_turnover($network_id) {
        $sql_omset_pribadi = "SELECT leadership_monthly_turnover_value,leadership_monthly_turnover_periode
                                    FROM sys_leadership_monthly_turnover
                                    WHERE leadership_monthly_turnover_network_id = '$network_id'
                                    AND leadership_monthly_turnover_sponsored_network_id = '$network_id'";
        $result = Yii::app()->db->createCommand($sql_omset_pribadi)->queryAll();
        return $result;
    }
    
    public static function get_turnover_group($network_id,$date) {
        //cari omset sponsornya 2 peringkat diatas
        $sql_peringkat_omset_group = "SELECT leadership_monthly_turnover_value
                                            FROM sys_leadership_monthly_turnover
                                            WHERE leadership_monthly_turnover_network_id = '$network_id'
                                            AND leadership_monthly_turnover_sponsored_network_id != '$network_id'
                                            AND leadership_monthly_turnover_periode = '" . $date . "'
                                            ORDER BY leadership_monthly_turnover_value DESC ";
        $result = Yii::app()->db->createCommand($sql_peringkat_omset_group)->queryAll();
        return $result;
    }
    
    //report reward
    public static function get_data_reward($network_id, $position) {
        $sql = "SELECT count(reward_netgrow_network_id) FROM sys_reward_warehouse_netgrow WHERE reward_netgrow_network_id = '" . $network_id . "' and reward_netgrow_position='".$position."' LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }
    
    public static function get_data_reward_qord($network_id, $position) {
        $sql = "SELECT count(reward_netgrow_network_id) FROM sys_reward_qord_netgrow WHERE reward_netgrow_network_id = '" . $network_id . "' and reward_netgrow_position='".$position."' LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }
    
    public static function get_data_reward_haji($network_id, $position) {
        $sql = "SELECT count(reward_netgrow_network_id) FROM sys_reward_haji_plus_netgrow WHERE reward_netgrow_network_id = '" . $network_id . "' and reward_netgrow_position='".$position."' LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }
    //am hasanah
    public static function get_data_reward_wqhd($network_id, $position) {
        $sql = "SELECT count(reward_netgrow_network_id) FROM sys_reward_am_warehouse_netgrow WHERE reward_netgrow_network_id = '" . $network_id . "' and reward_netgrow_position='".$position."' LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

}